PostgreSQL 全文搜索 表的全文搜索

1 背景知识

本节展示如何全文搜索表数据，以及使用全文索引。

2 无索引的全文搜索

2.1 一个简单的全文搜索

1. 搜索影片信息表，description 字段包含 Woman 单词的记录。
2. Womanizer 和 Woman ，都会匹配到。
3. 这里使用显示参数 english 指明 预置规则。

SELECT description
FROM film
WHERE to_tsvector('english', description) @@ to_tsquery('english', 'Woman');

4. 查看执行计划。

EXPLAIN SELECT description
FROM film
WHERE to_tsvector('english', description) @@ to_tsquery('english', 'Woman');
//屏幕输出：
QUERY PLAN                                     
--------
 Seq Scan on film  (cost=0.00..317.50 rows=5 width=94)
   Filter: regconfig, description) @@ '''woman'''::tsquery
(2 rows)

4. 也可以忽略配置参数，使用 default_text_search_config 参数的值。

SELECT title ,description
FROM film
WHERE to_tsvector(description) @@ to_tsquery('Woman');

//屏幕输出：
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------
title       | STAMPEDE DISTURBING
description | A Unbelieveable Tale of a Woman And a Lumberjack who must Fight a Frisbee in A U-Boat
.... ..... ..... ........ 


-[ RECORD 136 ]----------------------------------------------------------------------------------------------------------------------------
title       | LAWRENCE LOVE
description | A Fanciful Yarn of a Database Administrator And a Mad Cow who must Pursue a Womanizer in Berlin

2.2 无索引的复杂查询

1. 查询影片表中 title 和 description 联合文本中，包含 DOOR 和 Woman 的文档。

SELECT title,description
FROM film
WHERE to_tsvector(title || ' ' || description) @@ to_tsquery('DOOR & Woman')
ORDER BY last_update DESC
LIMIT 10;

//屏幕输出：
-[ RECORD 1 ]------------------------------------------------------------------------------------------------
title       | DOORS PRESIDENT
description | A Awe-Inspiring Display of a Squirrel And a Woman who must Overcome a Boy in The Gulf of Mexico
-[ RECORD 2 ]------------------------------------------------------------------------------------------------
title       | MIXED DOORS
description | A Taut Drama of a Womanizer And a Lumberjack who must Succumb a Pioneer in Ancient India

3 GIN 索引加速查询

我们可以创建一个 GIN 索引来加速文本搜索查询。

3.1 GIN单列函数索引

1. 将special_features 字段改为text 类型。

ALTER TABLE film ALTER COLUMN description TYPE  text;

2. 在description字段上创建GIN单列索引。

CREATE INDEX film_idx ON film USING GIN(to_tsvector('english',description));

3. 查看执行计划。

EXPLAIN  SELECT title,description
FROM film
WHERE to_tsvector('english',description) @@ to_tsquery('Woman')
ORDER BY last_update DESC
LIMIT 10;

//屏幕输出：
QUERY PLAN                                                   
--------------------------------------------
 Limit  (cost=27.04..27.05 rows=5 width=117)
   ->  Sort  (cost=27.04..27.05 rows=5 width=117)
         Sort Key: last_update DESC
         ->  Bitmap Heap Scan on film  (cost=8.29..26.98 rows=5 width=117)
               Recheck Cond: regconfig, description) @@ to_tsquery('Woman'::text)
               ->  Bitmap Index Scan on film_idx  (cost=0.00..8.29 rows=5 width=0)
                     Index Cond: regconfig, description) @@ to_tsquery('Woman'::text)
(7 rows)

3.2 GIN联合函数索引

1. 在description字段上创建GIN函数索引。

CREATE INDEX film_idx2 
ON film 
USING GIN(to_tsvector('english', title || ' ' || description));

2. 查看执行计划。

EXPLAIN  SELECT title,description
FROM film
WHERE to_tsvector('english', title || ' ' || description) @@ to_tsquery('DOOR & Woman')
ORDER BY last_update DESC
LIMIT 10;

//屏幕输出：
 QUERY PLAN                                                    
-----------
 Limit  (cost=16.78..16.78 rows=1 width=117)
   ->  Sort  (cost=16.78..16.78 rows=1 width=117)
         Sort Key: last_update DESC
         ->  Bitmap Heap Scan on film  (cost=12.25..16.77 rows=1 width=117)
               Recheck Cond: regconfig, (((title)::text || ' '::text) || description) @@ to_tsquery('DOOR & Woma
n'::text))
               ->  Bitmap Index Scan on film_idx2  (cost=0.00..12.25 rows=1 width=0)
                     Index Cond: regconfig, (((title)::text || ' '::text) || description) @@ to_tsquery('DOOR & 
Woman'::text))
(7 rows)

3.3 添加字段，并创建GIN 索引加速查询

1. 创建 textsearchable_index_col 字段。
   textsearchable_index_col 字段是由 title 和 description 字段进行正规化后存储的数据。

ALTER TABLE film
    ADD COLUMN textsearchable_index_col tsvector
               GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(description, ''))) STORED;

2. 在 textsearchable_index_col 字段创建GIN 索引。

CREATE INDEX textsearch_idx ON film USING GIN(textsearchable_index_col);

3. 查看执行计划。
   从执行计划得出，全文搜索已经使用了索引。

EXPLAIN SELECT title,description
FROM film
WHERE textsearchable_index_col @@ to_tsquery('Woman')
ORDER BY last_update DESC
LIMIT 10;

//屏幕输出：
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Limit  (cost=25.79..25.80 rows=5 width=117)
   ->  Sort  (cost=25.79..25.80 rows=5 width=117)
         Sort Key: last_update DESC
         ->  Bitmap Heap Scan on film  (cost=8.29..25.73 rows=5 width=117)
               Recheck Cond: (textsearchable_index_col @@ to_tsquerytext)
               ->  Bitmap Index Scan on textsearch_idx  (cost=0.00..8.29 rows=5 width=0)
                     Index Cond: (textsearchable_index_col @@ to_tsquerytext)
(7 rows)

也可以使用 RUM 索引 和 Gist 索引 加速查询。